USE [DSRSystem_Temp]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spUpdateIndicatorDataTables]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spUpdateIndicatorDataTables]
GO

CREATE PROCEDURE [dbo].[spUpdateIndicatorDataTables]
	@CompanyIds NVARCHAR(MAX), @LastUpdate datetime
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)
	
	
	DECLARE @transaction_isolation_level SMALLINT;

	SELECT @transaction_isolation_level = transaction_isolation_level
    FROM sys.dm_exec_requests
    WHERE session_id = @@spid;

    print('spUpdateIndicatorDataTables transaction_isolation_level: ' + STR(@transaction_isolation_level))


	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.IndicatorDataTables') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[IndicatorDataTables](
			IndicatorId uniqueidentifier not null,
			CompanyId uniqueidentifier not null,
			IndicatorTemplateId uniqueidentifier not null,
			IndicatorNumber nvarchar(max) not null,
			Name nvarchar(max) null,  
			Year int null, 
			Value nvarchar(max) null
		) ON [PRIMARY]
	END


	declare @startTime datetime
	declare @endTime datetime
	declare @elapsedTime int
	declare @rowsAffected int

	select @startTime = getdate()


	DECLARE @Indicators TABLE
	(
		Id uniqueidentifier not null,
		CompanyId uniqueidentifier not null,
		TemplateId uniqueidentifier not null,
		Number nvarchar(max) null
	)
	INSERT INTO @Indicators
	SELECT	Indicators.Id, 
	        CompanyId, 
			TemplateId,
			Number
	FROM	dbo.Indicators AS Indicators
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON Indicators.CompanyId = SelectedCompanies.Id
	WHERE	(Indicators.Number = N'E.1.9' OR Indicators.Number = N'E.1.10')

	DECLARE @table TABLE
	(
		Name nvarchar(max) null, 
		Year int null, 
		Value nvarchar(max) null
	)
		
	DECLARE @IndicatorDataTables TABLE
	(
		IndicatorId uniqueidentifier not null,
		CompanyId uniqueidentifier not null,
		IndicatorTemplateId uniqueidentifier not null,
		IndicatorNumber nvarchar(max) null,
		--IsDataAvailable bit not null,
		--IsInsufficientDisclosure bit not null,
		Name nvarchar(max) null,  
		Year int null, 
		Value nvarchar(max) null
	)
		
	declare @indicatorId uniqueidentifier
	declare @companyId uniqueidentifier
	declare @indicatorTemplateId uniqueidentifier
	declare @indicatorNumber nvarchar(max)
	--declare @answerNumber int
	--declare @isDataAvailable bit
	--declare @isInsufficientDisclosure bit
		
	WHILE (SELECT Count(*) FROM @indicators) > 0 
		BEGIN 
				
			SELECT TOP 1 
				@indicatorId = Id,
				@companyId = CompanyId,
				@indicatorTemplateId = TemplateId,
				@indicatorNumber = Number
				--@answerNumber = AnswerNumber
			FROM @Indicators
				
			-- 7 = Data for this company is not yet available
			--SET @isDataAvailable = 1

			--IF @answerNumber = 7
			--	BEGIN
			--		SET @isDataAvailable = 0
			--	END
				
			-- for E.1.9 6 = Disclosure on carbon emissions is insufficient to determine performance relative to peers
			-- for E.1.10 6 = Disclosure is insufficient to calculate the company's carbon intensity trend over the last 3-5 years
			--SET @isInsufficientDisclosure = 0
				
			--IF @answerNumber = 6
			--	BEGIN
			--		SET @isInsufficientDisclosure = 1
			--	END
				
   		    INSERT INTO @table EXEC DSRSystem.dbo.GetIndicatorDataTable @IndicatorId


			IF (SELECT Count(*) FROM @table) > 0
				BEGIN
					INSERT INTO @IndicatorDataTables
					SELECT @indicatorId AS IndicatorId,
					       @companyId AS CompanyId,
						   @indicatorTemplateId AS IndicatorTemplateId,
						   
						   @indicatorNumber AS IndicatorNumber,
						   --@isDataAvailable AS IsDataAvailable,
						   --@isInsufficientDisclosure AS IsInsufficientDisclosure,
						   Name, 
						   Year, 
						   Value
					FROM  @table
				END
			ELSE
				BEGIN
					INSERT INTO @IndicatorDataTables
					VALUES
					(
						@indicatorId,
						@companyId,
						@indicatorTemplateId,
						@indicatorNumber,
						--@isDataAvailable,
						--@isInsufficientDisclosure,
						null, 
						null, 
						null
					)
				END

			DELETE FROM @table
				
			DELETE @Indicators WHERE Id = @indicatorId
		END



	DELETE IndicatorDataTables FROM dbo.IndicatorDataTables AS IndicatorDataTables
	LEFT OUTER JOIN dbo.FinalizedCompanies AS FinalizedCompanies ON IndicatorDataTables.CompanyId = FinalizedCompanies.CompanyId
	WHERE  (FinalizedCompanies.CompanyId IS NULL)   
	

	set @rowsAffected = @@ROWCOUNT

	select @endTime = getdate()
	select @elapsedTime = datediff(second, @startTime, @endTime)
	print 'Delete Indicator DataTables that do not belong to current companies => Elapsed time:' +  str(@elapsedTime) + ', RowsAffected:' + str(@rowsAffected)
	select @startTime = getdate()


	IF (@LastUpdate IS NULL) BEGIN
		TRUNCATE TABLE [dbo].[IndicatorDataTables] END 
	ELSE BEGIN
		DELETE IndicatorDataTables FROM dbo.IndicatorDataTables AS IndicatorDataTables
		INNER JOIN @SelectedCompanies AS SelectedCompanies ON IndicatorDataTables.CompanyId = SelectedCompanies.Id
	END

	set @rowsAffected = @@ROWCOUNT

	select @endTime = getdate()
	select @elapsedTime = datediff(second, @startTime, @endTime)
	print 'Delete Indicator DataTables that are going to be updated => Elapsed time:' +  str(@elapsedTime) + ', RowsAffected:' + str(@rowsAffected)
	select @startTime = getdate()



	INSERT INTO [dbo].[IndicatorDataTables]
	SELECT * FROM @IndicatorDataTables

	set @rowsAffected = @@ROWCOUNT

	select @endTime = getdate()
	select @elapsedTime = datediff(second, @startTime, @endTime)
	print 'Indicator DataTables have been updated => Elapsed time:' +  str(@elapsedTime) + ', RowsAffected:' + str(@rowsAffected)

END

GO


